#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

insert into hive.edu_dwb.wf_dwb_customer_relationship_wide
select
    a.id,
    a.create_date_time,
    a.deleted,
    a.customer_id,
    case when  a.origin_type in ('NETSERVICE','PRESIGNUP') then '线上'
        else  '线下'
        end as origin_type,  --orgin_type：线上线下，NETSERVICE or PRESIGNUP 表示线上，其他的值表示线下
    a.origin_channel,
    a.creator,
    a.itcast_school_id,
    a.itcast_subject_id,
    b.customer_relationship_id,
    case when c.clue_state='VALID_NEW_CLUES' then '新学员'
        else '老学员'
        end as clue_state ,--新老用户，VALID_NEW_CLUES是新线索
    b. id as cus_id,
    b.area,
    d.id as emp_id,
    d.tdepart_id,
    e.id as dep_id,
    e.name as dep_name,
    f.id as school_id,
    f.name as school_name,
    g.id    as subject_id,
    g.name  as subject_name
from hive.edu_dwd.wf_fact_customer_relationship a
left join hive.edu_dwd.wf_dim_customer b on a.customer_id=b.id
left join hive.edu_dwd.wf_dim_customer_clue c on a.id=c.customer_relationship_id
left join hive.edu_dwd.wf_dim_employee d on a.creator=d.id
left join hive.edu_dwd.wf_dim_scrm_department e on d.tdepart_id=e.id
left join hive.edu_dwd.wf_dim_itcast_school f on a.itcast_school_id=f.id
left join hive.edu_dwd.wf_dim_itcast_subject g on a.itcast_subject_id=g.id;


insert into hive.edu_dwb.wf_dwb_customer_clue_wide
select
    a.id,
    a.customer_relationship_id,
    a.create_date_time,
    case when a.clue_state='VALID_NEW_CLUES' then '新学员'
        else '老学员'
        end as clue_state ,
    c.id as rel_id,
    c.deleted,
    case when  c.origin_type in ('NETSERVICE','PRESIGNUP') then '线上'
     else  '线下'
     end as origin_type,
    b.customer_relationship_first_id,
    b.appeal_status
from hive.edu_dwd.wf_fact_customer_clue a
full join hive.edu_dwd.wf_dim_customer_appeal b on a.customer_relationship_id=b.customer_relationship_first_id
left join hive.edu_dwd.wf_dim_customer_relationship c on a.customer_relationship_id=c.id;"